package o2o.config.dao;

import com.jinyou.utils.common.ValidateUtil;
import o2o.config.dao.utils.DaoUtils;
import o2o.config.dao.utils.QueryCondition;
import org.apache.commons.lang.StringUtils;

import java.lang.reflect.*;
import java.math.BigInteger;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;

public class DaoImpl {
    private Connection conn;

    public Connection getConnection() throws SQLException {
        return conn;
    }

    // 关闭连接
    public void close(Connection conn, Statement stmt, ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
            if (stmt != null) {
                stmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public void close(Connection conn, Statement stmt) {
        close(conn, stmt, null);
    }

    public void close(Connection conn) {
        close(conn, null, null);
    }

    public Long saveObj(String tableName, Object obj) throws SQLException {
        Connection conn = getConnection();
        Long l = 0L;
        try {
            l = this.saveObj(tableName, obj, conn);
        } catch (SQLException e) {
            throw e;
        } finally {
            close(conn);
        }
        return l;
    }

    /**
     * @param tableName 表名
     * @param obj       对象
     * @param conn      数据库连接
     * @return
     * @throws SQLException
     */
    public Long saveObj(String tableName, Object obj, Connection conn) throws SQLException {
        if (StringUtils.isEmpty(tableName) || obj == null) {
            throw new SQLException("参数不能为空");
        }
        //获取对象的方法
        Method[] methods = obj.getClass().getDeclaredMethods();
        if (methods == null || methods.length < 1) {
            throw new SQLException("对象没有声明方法");
        }
        StringBuffer sql = new StringBuffer("INSERT INTO ").append(tableName).append("(");
        StringBuffer columns = new StringBuffer();
        StringBuffer wenhao = new StringBuffer();
        List<Object> values = new ArrayList<Object>();        //存放数值
        List<Type> types = new ArrayList<Type>();                //存放返回值类型
        for (int i = 0; i < methods.length; i++) {
            Method method = methods[i];

            String methodName = method.getName();
            if (!methodName.startsWith("get")) {
                continue;
            }
            String column = methodName.replace("get", "");
            column = column.substring(0, 1).toLowerCase() + column.substring(1);

            Object value = null;
            try {
                value = method.invoke(obj, new Object[0]);
            } catch (Exception e) {
                e.printStackTrace();
                continue;
            }
            if (value == null) {      //空值的不添加
                continue;
            }
            Type type = method.getGenericReturnType();    // 返回类型
            if (type instanceof ParameterizedType) {        //如果是泛型类型,跳过
                continue;
            } else if (type instanceof TypeVariable) {        //泛型擦拭对象，跳过
                continue;
            } else if (type instanceof GenericArrayType) {    //数组泛型,跳过
                continue;
            } else {
//                System.out.print("参数类型：" + type + "		");
//                System.out.print("参数名称：" + column + "		");
//                System.out.println("参数数值：" + value.toString());
            }
            types.add(type);
            values.add(value);

            columns.append(column).append(",");
            wenhao.append("?,");
        }
        sql.append(columns.substring(0, columns.length() - 1));
        sql.append(")values(");
        sql.append(wenhao.substring(0, wenhao.length() - 1));
        sql.append(")");
//        System.out.println(sql);

        //开始插入
        PreparedStatement pre = conn.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
        if (values.size() - types.size() != 0) {
            throw new SQLException("插入对象过程中参数遍历出现错误");
        }
        for (int i = 0; i < values.size(); i++) {
            Type type = types.get(i);
            if (type.toString().indexOf("java.lang.String") >= 0) {
                pre.setString(i + 1, values.get(i).toString());
            } else if (type.toString().indexOf("int") >= 0 || type.toString().indexOf("java.lang.Integer") >= 0) {
                pre.setInt(i + 1, Integer.parseInt(values.get(i).toString()));
            } else if (type.toString().indexOf("double") >= 0 || type.toString().indexOf("java.lang.Double") >= 0) {
                pre.setDouble(i + 1, Double.parseDouble(values.get(i).toString()));
            } else if (type.toString().indexOf("long") >= 0 || type.toString().indexOf("java.lang.Long") >= 0) {
                pre.setLong(i + 1, Long.parseLong(values.get(i).toString()));
            } else if (type.toString().indexOf("date") >= 0 || type.toString().indexOf("java.util.Date") >= 0) {
                pre.setTimestamp(i + 1, new Timestamp(((Date) values.get(i)).getTime()));
            }
        }
        pre.addBatch();
        try {
            boolean b = pre.execute();
            ResultSet rs = pre.getGeneratedKeys();
            if (rs.next()) {
                return rs.getLong(1);
            }
            return -1L;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        }
    }

    /**
     * Jahnke 2020年10月24日 10:43:33 增加数据批量插入
     *
     * @param tableName 表名
     * @param objs      对象
     * @return 命令成功执行后返回它所影响到的行的数目 egs：[1,1,0]  列表第三个执行失败，前两个执行成功
     * @throws SQLException
     */
    public int[] saveObjs(String tableName, Class clazz, List<? extends Object> objs) throws SQLException {
        if (StringUtils.isEmpty(tableName) || objs == null) {
            throw new SQLException("参数不能为空");
        }
        //获取对象的方法
        Method[] methods = clazz.getDeclaredMethods();
        if (methods == null || methods.length < 1) {
            throw new SQLException("对象没有声明方法");
        }
        Connection conn = getConnection();

        StringBuffer columns = new StringBuffer();
        StringBuffer wenhao = new StringBuffer();                // 存储问号
        StringBuffer sql = new StringBuffer("INSERT INTO ").append(tableName).append("(");
        List<List<Object>> valuesList = new ArrayList<>();           // 存放数据
        List<Type> types = new ArrayList<Type>();                // 存放返回值类型
        for (int m = 0; m < objs.size(); m++) {
            List<Object> values = new ArrayList<>();
            for (int i = 0; i < methods.length; i++) {
                Method method = methods[i];

                String methodName = method.getName();
                if (!methodName.startsWith("get")) {
                    continue;
                }
                Object value = null;
                String column = methodName.replace("get", "");
                column = column.substring(0, 1).toLowerCase() + column.substring(1);

                try {
                    value = method.invoke(objs.get(m), new Object[0]);
                } catch (Exception e) {
                    e.printStackTrace();
                    continue;
                }
                if (value == null) {      //空值的不添加
                    continue;
                }
                if (m == 0) {
                    Type type = method.getGenericReturnType();    // 返回类型
                    if (type instanceof ParameterizedType) {        //如果是泛型类型,跳过
                        continue;
                    } else if (type instanceof TypeVariable) {        //泛型擦拭对象，跳过
                        continue;
                    } else if (type instanceof GenericArrayType) {    //数组泛型,跳过
                        continue;
                    } else {
                        // System.out.print("参数类型：" + type + "		");
                        // System.out.print("参数名称：" + column + "		");
                        // System.out.println("参数数值：" + value.toString());
                    }
                    types.add(type);

                    columns.append(column).append(",");
                    wenhao.append("?,");
                }
                values.add(value);
            }
            valuesList.add(values);
        }
        sql.append(columns.substring(0, columns.length() - 1));
        sql.append(")values(");
        sql.append(wenhao.substring(0, wenhao.length() - 1));
        sql.append(")");

        //开始插入
        PreparedStatement pre = conn.prepareStatement(sql.toString(), Statement.RETURN_GENERATED_KEYS);
        if (valuesList.get(0).size() - types.size() != 0) {
            throw new SQLException("插入对象过程中参数遍历出现错误");
        }
        for (int n = 0; n < valuesList.size(); n++) {
            List<Object> values = valuesList.get(n);
            for (int i = 0; i < values.size(); i++) {
                Type type = types.get(i);
                if (type.toString().indexOf("int") >= 0 || type.toString().indexOf("java.lang.Integer") >= 0) {
                    pre.setInt(i + 1, Integer.parseInt(values.get(i).toString()));
                } else if (type.toString().indexOf("java.lang.String") >= 0) {
                    pre.setString(i + 1, values.get(i).toString());
                } else if (type.toString().indexOf("double") >= 0 || type.toString().indexOf("java.lang.Double") >= 0) {
                    pre.setDouble(i + 1, Double.parseDouble(values.get(i).toString()));
                } else if (type.toString().indexOf("long") >= 0 || type.toString().indexOf("java.lang.Long") >= 0) {
                    pre.setLong(i + 1, Long.parseLong(values.get(i).toString()));
                } else if (type.toString().indexOf("date") >= 0 || type.toString().indexOf("java.util.Date") >= 0) {
                    pre.setTimestamp(i + 1, new Timestamp(((Date) values.get(i)).getTime()));
                }
            }
            pre.addBatch();
        }
        try {
            int[] b = pre.executeBatch();
            pre.clearBatch();
            return b;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            close(conn);
        }
    }


    /*
     * 插入数据库
     * Jahnke  2016年12月5日 17:03:28
     */
    @Deprecated
    public boolean insertObj(String tableName, Object obj) throws SQLException {
        if (ValidateUtil.isNull(tableName) || obj == null) {
            throw new SQLException("参数不能为空");
        }
        //获取对象的方法
        Method[] methods = obj.getClass().getDeclaredMethods();
        if (methods == null || methods.length < 1) {
            throw new SQLException("对象没有声明方法");
        }
        StringBuffer sql = new StringBuffer("INSERT INTO ").append(tableName).append("(");
        StringBuffer columns = new StringBuffer();
        StringBuffer wenhao = new StringBuffer();
        List<Object> values = new ArrayList<Object>();        //存放数值
        List<Type> types = new ArrayList<Type>();                //存放返回值类型
        for (int i = 0; i < methods.length; i++) {
            Method method = methods[i];

            String methodName = method.getName();
            if (!methodName.startsWith("get")) {
                continue;
            }
            String column = methodName.replace("get", "");
            column = column.substring(0, 1).toLowerCase() + column.substring(1);

            Object value = null;
            try {
                value = method.invoke(obj, new Object[0]);
            } catch (Exception e) {
                e.printStackTrace();
                continue;
            }
            if (value == null) {      //空值的不添加
                continue;
            }
            Type type = method.getGenericReturnType();    // 返回类型
            if (type instanceof ParameterizedType) {        //如果是泛型类型,跳过
                continue;
            } else if (type instanceof TypeVariable) {        //泛型擦拭对象，跳过
                continue;
            } else if (type instanceof GenericArrayType) {    //数组泛型,跳过
                continue;
            } else {
//                System.out.print("参数类型：" + type + "		");
//                System.out.print("参数名称：" + column + "		");
//                System.out.println("参数数值：" + value.toString());
            }
            types.add(type);
            values.add(value);

            columns.append(column).append(",");
            wenhao.append("?,");
        }
        sql.append(columns.substring(0, columns.length() - 1));
        sql.append(")values(");
        sql.append(wenhao.substring(0, wenhao.length() - 1));
        sql.append(")");
//		System.out.println(sql);

        //开始插入
        Connection conn = getConnection();
        PreparedStatement pre = conn.prepareStatement(sql.toString());
        if (values.size() - types.size() != 0) {
            throw new SQLException("插入对象过程中参数遍历出现错误");
        }
        for (int i = 0; i < values.size(); i++) {
            Type type = types.get(i);
            if (type.toString().indexOf("java.lang.String") >= 0) {
                pre.setString(i + 1, values.get(i).toString());
            } else if (type.toString().indexOf("int") >= 0 || type.toString().indexOf("java.lang.Integer") >= 0) {
                pre.setInt(i + 1, Integer.parseInt(values.get(i).toString()));
            } else if (type.toString().indexOf("double") >= 0 || type.toString().indexOf("java.lang.Double") >= 0) {
                pre.setDouble(i + 1, Double.parseDouble(values.get(i).toString()));
            } else if (type.toString().indexOf("long") >= 0 || type.toString().indexOf("java.lang.Long") >= 0) {
                pre.setLong(i + 1, Long.parseLong(values.get(i).toString()));
            }
        }
        pre.addBatch();
        System.out.println(pre.toString());
        try {
            boolean b = pre.execute();
            return b;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            pre.close();
            conn.close();
        }
    }

    //查询查询个数
    public int getCount(String table, QueryCondition query) throws SQLException {
        //拼接sql
        StringBuffer buf = new StringBuffer("SELECT IFNULL(COUNT(1),0) as counts from ").append(table).append(" ");
        StringBuffer sql = new StringBuffer();
        sql.append(DaoUtils.getWhereSql(buf.toString(), query));

        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql.toString());
            ResultSetMetaData md = rs.getMetaData();
            while (rs.next()) {
                return Integer.parseInt(rs.getString("counts"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return 0;
    }

    public Long getCount(String sql) throws SQLException {
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData();
            while (rs.next()) {
                return Long.parseLong(rs.getString("counts"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return 0l;
    }

    /**
     * @throws SQLException
     * @throws
     * @方法名: queryMapBySql
     * @方法描述:返回单个对象
     * @方法参数 @param sql
     * @方法参数 @return
     * @返回类型 HashMap<String, Object>
     * @返回类型 HashMap<String, Object>
     */
    public HashMap<String, Object> queryMapBySql(String sql) throws SQLException {
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData();
            int num = md.getColumnCount();
            while (rs.next()) {
                HashMap<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= num; i++) {
                    map.put(md.getColumnLabel(i), rs.getObject(i));
                }
                return map;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return null;
    }

    /**
     * 根据sql语句查询数据库
     *
     * @param sql
     * @return
     * @throws SQLException
     */
    public List<HashMap<String, Object>> queryBySql(String sql) throws SQLException {
        Connection conn = getConnection();
        List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData();
            int num = md.getColumnCount();
            while (rs.next()) {
                HashMap<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= num; i++) {
                    map.put(md.getColumnLabel(i), rs.getObject(i));
                }
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return null;
    }

    /**
     * /**
     *
     * @param clazz
     * @param sql
     * @return
     * @throws SQLException
     * @author Jahnke 2016年3月12日21:46:20
     */
    public List<? extends Object> list(Class clazz, String sql) throws SQLException {
        // 根据类名来创建Class对象
        Class c = null;
        try {
            c = Class.forName(clazz.getName());
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection conn = getConnection();
        List<Object> list = new ArrayList<Object>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
//            Long time = DateUtils.getCurrTime();
            // 创建类的实例
            Object obj = null;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            // ----v2
            HashMap<String, String> map = new HashMap<>();
            // 将类所有字段 按照 全小写名称.名称 来放入map
            Field[] fields = clazz.getDeclaredFields();
            for (Field fd : fields) {
                String fieldName = fd.getName();
                String fieldNameLC = fd.getName().toLowerCase();
                map.put(fieldNameLC, fieldName);
            }
            // 数据库查询结果的列名
            HashMap<String, String> colMap = new HashMap<>();
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                String label = rsmd.getColumnLabel(i);
                // 先通过列名查询有没有该字段
                if (map.containsKey(label.toLowerCase())) {
                    colMap.put(map.get(label.toLowerCase()), rsmd.getColumnLabel(i));
                } else if (label.contains("_")){
                    // 如果查询没有, 且列包含下划线的话, 去除下划线之后再次尝试查找字段
                    String columnName = rsmd.getColumnLabel(i).replaceAll("_", "").toLowerCase();
                    if (map.containsKey(columnName)) {
                        colMap.put(map.get(columnName), rsmd.getColumnLabel(i));
                    }
                }
            }

            while (rs.next()) {
                //利用反射获取，执行类的实例化对象
                obj = c.newInstance();

                // 遍历查到列，赋值
                for (String columnName : colMap.keySet()) {
                    Field fd = clazz.getDeclaredField(columnName);
                    String columnLabel = colMap.get(columnName);
                    fd.setAccessible(true);
                    // 得到方法的参数类型
                    Class type = fd.getType();
                    if (type == Long.class || type == long.class) {
                        // 如果参数为long类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                        fd.set(obj, rs.getLong(columnLabel));
                    } else if (type == String.class) {
                        // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                        fd.set(obj, rs.getString(columnLabel));
                    } else if (type == int.class || type == Integer.class) {
                        fd.set(obj, rs.getInt(columnLabel));
                    } else if (type == BigInteger.class) {
                        fd.set(obj, String.valueOf(rs.getBigDecimal(columnLabel)));
                    } else if (type == Date.class) {
                        if (rs.getDate(columnLabel) == null) {
                            fd.set(obj, rs.getTimestamp(columnLabel));
                        } else {
                            fd.set(obj, new Date(rs.getTimestamp(columnLabel).getTime()));
                        }
                    } else if (type == Double.class || type == double.class) {
                        fd.set(obj, rs.getDouble(columnLabel));
                    }
                }
                //返回转换后的集合
                list.add(obj);
            }
            // ---end v2

//            Long time2 = DateUtils.getCurrTime();
//            System.out.println(sql + " : Q[" + (time2 - time) + "] :S[" + (time2 - time1) + "]");
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return null;
    }

    /**
     * 解决list方法 指定列明时 不能映射到指定字段问题
     *
     * @param clazz
     * @param sql
     * @return
     * @throws SQLException
     * @author yangtk 2020年7月29日11:45:33
     */
    public List<? extends Object> listByColumnLabel(Class clazz, String sql) throws SQLException {
        // 根据类名来创建Class对象
        Class c = null;
        try {
            c = Class.forName(clazz.getName());
        } catch (Exception e) {
            e.printStackTrace();
        }
        Connection conn = getConnection();
        List<Object> list = new ArrayList<Object>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
//            Long time = DateUtils.getCurrTime();
            // 创建类的实例
            Object obj = null;
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            HashMap<String, String> map = new HashMap<>();
            Field[] fields = clazz.getDeclaredFields();
            for (Field fd : fields) {
                String fieldName = fd.getName();
                String fieldNameLC = fd.getName().toLowerCase();
                map.put(fieldNameLC, fieldName);
            }


            HashSet<String> cols = new HashSet<>();
            ResultSetMetaData rsmd = rs.getMetaData();
            for (int i = 1; i < rsmd.getColumnCount() + 1; i++) {
                String columnName = rsmd.getColumnLabel(i).toLowerCase();
                if (!map.containsKey(columnName)) {
                    continue;
                }
                cols.add(map.get(columnName));
            }

            while (rs.next()) {
                //利用反射获取，执行类的实例化对象
                obj = c.newInstance();

                // 遍历查到列，赋值
                for (String columnName : cols) {
                    Field fd = clazz.getDeclaredField(columnName);
                    fd.setAccessible(true);
                    // 得到方法的参数类型
                    Class type = fd.getType();
                    if (type == Long.class || type == long.class) {
                        // 如果参数为long类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                        fd.set(obj, rs.getLong(columnName));
                    } else if (type == String.class) {
                        // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                        fd.set(obj, rs.getString(columnName));
                    } else if (type == int.class || type == Integer.class) {
                        fd.set(obj, rs.getInt(columnName));
                    } else if (type == BigInteger.class) {
                        fd.set(obj, String.valueOf(rs.getBigDecimal(columnName)));
                    } else if (type == Date.class) {
                        if (rs.getDate(columnName) == null) {
                            fd.set(obj, rs.getTimestamp(columnName));
                        } else {
                            fd.set(obj, new Date(rs.getTimestamp(columnName).getTime()));
                        }
                    } else if (type == Double.class || type == double.class) {
                        fd.set(obj, rs.getDouble(columnName));
                    }
                }
                //返回转换后的集合
                list.add(obj);
            }
            // ---end v2

//            Long time2 = DateUtils.getCurrTime();
//            System.out.println(sql + " : Q[" + (time2 - time) + "] :S[" + (time2 - time1) + "]");
            return list;
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return null;
    }

    public Object getObject(Class clazz, String sql) throws SQLException {
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;

        // 根据类名来创建Class对象
        Class c = null;
        try {
            c = Class.forName(clazz.getName());
        } catch (Exception e) {
            e.printStackTrace();
        }

        // 创建类的实例
        Object obj = null;
        try {
            stmt = conn.createStatement();
            // 得到执行查寻语句返回的结果集
            rs = stmt.executeQuery(sql);
            // 得到对象的方法数组
            Method[] methods = c.getMethods();
            // 遍历结果集
            while (rs.next()) {
                obj = c.newInstance();
                for (Method method : methods) {
                    String methodName = method.getName();
                    if (methodName.startsWith("set")) {
                        // 根据方法名字得到数据表格中字段的名字
                        String columnName = methodName.substring(3, 4)
                                .toLowerCase() + methodName.substring(4);

                        // 首先判断时候存在此列，不存在则不赋值
                        if (!isExistColumn(rs, columnName)) {
                            continue;
                        }

                        // 得到方法的参数类型
                        Class[] parmts = method.getParameterTypes();
                        if (parmts[0] == Long.class || parmts[0] == long.class) {
                            // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                            method.invoke(obj, rs.getLong(columnName));
                        }
                        if (parmts[0] == String.class) {
                            // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                            method.invoke(obj, rs.getString(columnName));
                        }
                        if (parmts[0] == int.class || parmts[0] == Integer.class) {
                            method.invoke(obj, rs.getInt(columnName));
                        }
                        // TODO:解析BigInteger
                        if (parmts[0] == BigInteger.class) {
                            method.invoke(obj, String.valueOf(rs
                                    .getBigDecimal(columnName)));
                        }
                        if (parmts[0] == Date.class) {
                            if (rs.getDate(columnName) == null) {
                                method.invoke(obj, rs.getTimestamp(columnName));
                            } else {
//                                method.invoke(obj, Common.sdfDateTime
//                                        .format(new Date(rs.getTimestamp(
//                                                columnName).getTime())));
                                method.invoke(obj, new Date(rs.getTimestamp(
                                        columnName).getTime()));
                            }

                        }
                        if (parmts[0] == Double.class || parmts[0] == double.class) {
                            method.invoke(obj, rs.getDouble(columnName));
                        }
                    }
                }
                return obj;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return obj;
    }

    public static final ThreadLocal<DateFormat> sdfDateTime = ThreadLocal.withInitial(() -> {
        return new SimpleDateFormat("yyyy-MM-dd HH:mm:ss", Locale.SIMPLIFIED_CHINESE);
    });

    /**
     * 从数据库中取得对象      * @author Jahnke 2016年3月12日20:36:32
     *
     * @param clazz
     * @param tableName
     * @return 需要查找的对象
     * @throws SQLException
     */
    public Object getObject(Class clazz, String tableName, long i,
                            Boolean delFalg) throws SQLException {
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;

        // 根据类名来创建Class对象
        Class c = null;
        try {
            c = Class.forName(clazz.getName());
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 拼凑查询sql语句
        String sql = "select * from "
                + tableName
                + " where Id="
                + i
                + (delFalg == null || delFalg == false ? " and delFlag = 1"
                : " and delFlag=0");
        System.out.println(sql);
        // 创建类的实例
        Object obj = null;
        try {
            stmt = conn.createStatement();
            // 得到执行查寻语句返回的结果集
            rs = stmt.executeQuery(sql);
            // 得到对象的方法数组
            Method[] methods = c.getMethods();
            // 遍历结果集
            while (rs.next()) {
                obj = c.newInstance();
                for (Method method : methods) {
                    String methodName = method.getName();
                    if (methodName.startsWith("set")) {
                        // 根据方法名字得到数据表格中字段的名字
                        String columnName = methodName.substring(3, 4)
                                .toLowerCase() + methodName.substring(4);

                        // 首先判断时候存在此列，不存在则不赋值
                        if (!isExistColumn(rs, columnName)) {
                            continue;
                        }

                        // 得到方法的参数类型
                        Class[] parmts = method.getParameterTypes();
                        if (parmts[0] == Long.class || parmts[0] == long.class) {
                            // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                            method.invoke(obj, rs.getLong(columnName));
                        }
                        if (parmts[0] == String.class) {
                            // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                            method.invoke(obj, rs.getString(columnName));
                        }
                        if (parmts[0] == int.class || parmts[0] == Integer.class) {
                            method.invoke(obj, rs.getInt(columnName));
                        }
                        // TODO:解析BigInteger
                        if (parmts[0] == BigInteger.class) {
                            method.invoke(obj, String.valueOf(rs
                                    .getBigDecimal(columnName)));
                        }
//                        if (parmts[0] == Date.class) {
//                            if (rs.getDate(columnName) == null) {
//                                method.invoke(obj, rs.getTimestamp(columnName));
//                            } else {
//                                method.invoke(obj, sdfDateTime.get()
//                                        .format(new Date(rs.getTimestamp(
//                                                columnName).getTime())));
//                            }
//
//                        }

                        if (parmts[0] == Date.class) {
                            if (rs.getDate(columnName) == null) {
                                method.invoke(obj, rs.getTimestamp(columnName));
                            } else {
                                method.invoke(obj, new Date(rs.getTimestamp(
                                        columnName).getTime()));
                            }
                        }
                        if (parmts[0] == Double.class || parmts[0] == double.class) {
                            method.invoke(obj, rs.getDouble(columnName));
                        }
                    }
                }
                return obj;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return obj;
    }

    /**
     * @param @param  clazz
     * @param @param  tableName
     * @param @param  column
     * @param @param  value
     * @param @param  delFalg null或者false,表示未删除的数据
     * @param @return
     * @return Object
     * @throws SQLException
     * @throws
     * @Description: 根据model，表名，以及具体的信息获取单一对象
     * @author Jahnke
     * @date 2016-3-18
     */
    public Object getObject(Class clazz, String tableName, String column,
                            String value, int delFalg) throws SQLException {
        Connection conn = getConnection();
        Statement stmt = null;
        ResultSet rs = null;

        // 根据类名来创建Class对象
        Class c = null;
        try {
            c = Class.forName(clazz.getName());
        } catch (Exception e) {
            e.printStackTrace();
        }
        // 拼凑查询sql语句
        String sql = "select * from "
                + tableName
                + " where "
                + column
                + " like '"
                + value
                + "%'"
                + " and delFlag = " + delFalg
                + " limit 1 ";
        System.out.println(sql);
        // 创建类的实例
        Object obj = null;
        try {
            stmt = conn.createStatement();
            // 得到执行查寻语句返回的结果集
            rs = stmt.executeQuery(sql);
            // 得到对象的方法数组
            Method[] methods = c.getMethods();
            // 遍历结果集
            while (rs.next()) {
                obj = c.newInstance();
                for (Method method : methods) {
                    String methodName = method.getName();
                    if (methodName.startsWith("set")) {
                        // 根据方法名字得到数据表格中字段的名字
                        String columnName = methodName.substring(3, 4)
                                .toLowerCase() + methodName.substring(4);

                        // 首先判断时候存在此列，不存在则不赋值
                        if (!isExistColumn(rs, columnName)) {
                            continue;
                        }

                        // 得到方法的参数类型
                        Class[] parmts = method.getParameterTypes();
                        if (parmts[0] == Long.class || parmts[0] == long.class) {
                            // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                            method.invoke(obj, rs.getLong(columnName));
                        }
                        if (parmts[0] == String.class) {
                            // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                            method.invoke(obj, rs.getString(columnName));
                        }
                        if (parmts[0] == int.class || parmts[0] == Integer.class) {
                            method.invoke(obj, rs.getInt(columnName));
                        }
                        // TODO:解析BigInteger
                        if (parmts[0] == BigInteger.class) {
                            method.invoke(obj, String.valueOf(rs
                                    .getBigDecimal(columnName)));
                        }
//                        if (parmts[0] == Date.class) {
//                            if (rs.getDate(columnName) == null) {
//                                method.invoke(obj, rs.getTimestamp(columnName));
//                            } else {
//                                method.invoke(obj, sdfDateTime.get()
//                                        .format(new Date(rs.getTimestamp(
//                                                columnName).getTime())));
//                            }
//
//                        }
                        if (parmts[0] == Date.class) {
                            if (rs.getDate(columnName) == null) {
                                method.invoke(obj, rs.getTimestamp(columnName));
                            } else {
                                method.invoke(obj, new Date(rs.getTimestamp(
                                        columnName).getTime()));
                            }
                        }
                        if (parmts[0] == Double.class || parmts[0] == double.class) {
                            method.invoke(obj, rs.getDouble(columnName));
                        }
                    }
                }
                return obj;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return obj;
    }

    // 单个更新
    public boolean updateOneObj(String tableName, Object obj, Map<String, Object> querys) throws SQLException {
        if (querys == null || !querys.isEmpty()) {
            throw new SQLException("查询条件不能为空");
        }
        return this.updateObj(tableName, obj, querys);
    }

    public boolean updateObj(String tableName, Object obj, String columnName, Object keyValue) throws SQLException {
        Map<String, Object> query = new HashMap<String, Object>();
        query.put(columnName, keyValue);
        return this.updateObj(tableName, obj, query);
    }

    public boolean updateObj(String tableName, Object obj, Map<String, Object> querys, Connection conn) throws SQLException {
        if (tableName == null || tableName.equals("")) {
            throw new SQLException("参数不能为空");
        }
        boolean isMainKey = false;      //是否存在where查询条件。不存在无法执行
        boolean isUpdate = false;       //是否存在需要更新的地方

        StringBuilder sql = new StringBuilder("UPDATE ").append(tableName).append(" ");
        StringBuilder setParams = new StringBuilder();

        Method[] methods = obj.getClass().getMethods();
        if (methods == null || methods.length < 1) {
            throw new SQLException("对象没有声明方法");
        }
        List<Object> values = new ArrayList<Object>();        //存放数值
        List<Type> types = new ArrayList<Type>();                //存放返回值类型
        for (int i = 0; i < methods.length; i++) {
            Method method = methods[i];
            String methodName = method.getName();

            if (!methodName.startsWith("get")) {
                continue;
            }
            String column = methodName.replace("get", "");
            column = column.substring(0, 1).toLowerCase() + column.substring(1);

            Object value = null;
            try {
                value = method.invoke(obj, new Object[0]);
            } catch (Exception e) {
                e.printStackTrace();
                continue;
            }
            if (value == null) {      //空值的不添加
                continue;
            }
            Type type = method.getGenericReturnType();    // 返回类型
            if (type instanceof ParameterizedType) {        //如果是泛型类型,跳过
                continue;
            } else if (type instanceof TypeVariable) {        //泛型擦拭对象，跳过
                continue;
            } else if (type instanceof GenericArrayType) {    //数组泛型,跳过
                continue;
            }
            types.add(type);
            values.add(value);
            if (!isUpdate) {
                setParams.append("set ").append(column).append("=?");
                isUpdate = true;
            } else {
                setParams.append(",").append(column).append("=?");
            }
        }

        if (querys != null && !querys.isEmpty()) {
            sql.append(setParams).append(" WHERE ");
            int i = -1;
            for (Map.Entry<String, Object> data : querys.entrySet()) {
                i++;
                if (i == 0) {
                    sql.append(data.getKey()).append("='").append(data.getValue().toString()).append("'");
                    continue;
                }
                sql.append(" and ").append(data.getKey()).append("='").append(data.getValue().toString()).append("'");
            }
        }

        PreparedStatement pre = conn.prepareStatement(sql.toString());
        if (!isUpdate) {
            throw new SQLException("对象不存在需要更新的参数");
        }
        if (values.size() - types.size() != 0) {
            throw new SQLException("插入对象过程中参数遍历出现错误");
        }
        for (int i = 0; i < values.size(); i++) {
            Type type = types.get(i);
            if (type.toString().indexOf("java.lang.String") >= 0) {
                pre.setString(i + 1, values.get(i).toString());
            } else if (type.toString().indexOf("int") >= 0 || type.toString().indexOf("java.lang.Integer") >= 0) {
                pre.setInt(i + 1, Integer.parseInt(values.get(i).toString()));
            } else if (type.toString().indexOf("double") >= 0 || type.toString().indexOf("java.lang.Double") >= 0) {
                pre.setDouble(i + 1, Double.parseDouble(values.get(i).toString()));
            } else if (type.toString().indexOf("long") >= 0 || type.toString().indexOf("java.lang.Long") >= 0) {
                pre.setLong(i + 1, Long.parseLong(values.get(i).toString()));
            } else if (type.toString().indexOf("date") >= 0 || type.toString().indexOf("java.util.Date") >= 0) {
                pre.setTimestamp(i + 1, new Timestamp(((Date) values.get(i)).getTime()));
            }
        }
        pre.addBatch();
        try {
            boolean b = pre.execute();
            return b;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        } finally {
            pre.close();
        }
    }

    /**
     * @param tableName 表名
     * @param obj       对象
     * @param querys    查询条件key-value
     * @return
     * @throws SQLException
     */
    public boolean updateObj(String tableName, Object obj, Map<String, Object> querys) throws SQLException {
        Connection conn = getConnection();
        boolean f = updateObj(tableName, obj, querys, conn);
        close(conn);
        return f;
    }

    public boolean updateObj(String tableName, Object obj, String columnName, Object keyValue, Connection conn) throws SQLException {
        if (tableName == null || tableName.equals("")) {
            throw new SQLException("参数不能为空");
        }
        boolean isMainKey = false;      //是否存在where查询条件。不存在无法执行
        boolean isUpdate = false;       //是否存在需要更新的地方

        StringBuilder sql = new StringBuilder("UPDATE ").append(tableName).append(" ");
        StringBuilder setParams = new StringBuilder();
        if (columnName == null || columnName.equals("") || keyValue == null) {     //查询条件
            throw new SQLException("where查询条件不能为空");
        }
        Method[] methods = obj.getClass().getMethods();
        if (methods == null || methods.length < 1) {
            throw new SQLException("对象没有声明方法");
        }
        List<Object> values = new ArrayList<Object>();        //存放数值
        List<Type> types = new ArrayList<Type>();                //存放返回值类型
        for (int i = 0; i < methods.length; i++) {
            Method method = methods[i];
            String methodName = method.getName();

            if (!methodName.startsWith("get")) {
                continue;
            }
            String column = methodName.replace("get", "");
            column = column.substring(0, 1).toLowerCase() + column.substring(1);

            Object value = null;
            try {
                value = method.invoke(obj, new Object[0]);
            } catch (Exception e) {
                e.printStackTrace();
                continue;
            }
            if (value == null) {      //空值的不添加
                continue;
            }
            Type type = method.getGenericReturnType();    // 返回类型
            if (type instanceof ParameterizedType) {        //如果是泛型类型,跳过
                continue;
            } else if (type instanceof TypeVariable) {        //泛型擦拭对象，跳过
                continue;
            } else if (type instanceof GenericArrayType) {    //数组泛型,跳过
                continue;
            }
            types.add(type);
            values.add(value);
            if (!isUpdate) {
                setParams.append("set ").append(column).append("=?");
                isUpdate = true;
            } else {
                setParams.append(",").append(column).append("=?");
            }
        }
        sql.append(setParams).append(" WHERE ").append(columnName).append("='").append(keyValue).append("'");
        //prepare预更新语句已拼接完成
//        System.out.println(sql);

        PreparedStatement pre = conn.prepareStatement(sql.toString());
        if (!isUpdate) {
            throw new SQLException("对象不存在需要更新的参数");
        }
        if (values.size() - types.size() != 0) {
            throw new SQLException("插入对象过程中参数遍历出现错误");
        }
        for (int i = 0; i < values.size(); i++) {
            Type type = types.get(i);
            if (type.toString().indexOf("java.lang.String") >= 0) {
                pre.setString(i + 1, values.get(i).toString());
            } else if (type.toString().indexOf("int") >= 0 || type.toString().indexOf("java.lang.Integer") >= 0) {
                pre.setInt(i + 1, Integer.parseInt(values.get(i).toString()));
            } else if (type.toString().indexOf("double") >= 0 || type.toString().indexOf("java.lang.Double") >= 0) {
                pre.setDouble(i + 1, Double.parseDouble(values.get(i).toString()));
            } else if (type.toString().indexOf("long") >= 0 || type.toString().indexOf("java.lang.Long") >= 0) {
                pre.setLong(i + 1, Long.parseLong(values.get(i).toString()));
            } else if (type.toString().indexOf("date") >= 0 || type.toString().indexOf("java.util.Date") >= 0) {
                pre.setTimestamp(i + 1, new Timestamp(((Date) values.get(i)).getTime()));
            }
        }
        pre.addBatch();
        try {
            boolean b = pre.execute();
            return b;
        } catch (Exception e) {
            throw new SQLException(e.getMessage());
        }
    }

    public void updateMap(String tableName, Map<String, Object> map, String column, Object value) throws SQLException {
        if (StringUtils.isEmpty(tableName) || map == null || map.size() < 1) {
            throw new SQLException("参数不能为空");
        }
        StringBuffer sql = new StringBuffer("update ").append(tableName);
        int i = 0;
        for (String key : map.keySet()) {
            if (i == 0) {
                sql.append("set ").append(key).append("='" + map.get(key) + "'");
            } else {
                sql.append(",").append(key).append("='" + map.get(key) + "'");
            }
            i++;
        }
        sql.append(" where ").append(column).append("='").append(value.toString()).append("'");
        //执行sql
        executeUpdateBySql(sql.toString());
    }

    /**
     * @param @param  tableName
     * @param @param  id
     * @param @throws SQLException
     * @return void
     * @throws
     * @Description: 通过表名和主键 惊醒删除操作
     * @author Jahnke
     * @date 2016-3-23
     */
    public void delete(String tableName, Long id)
            throws SQLException {
        Connection conn = getConnection();
        String sql = "delete from " + tableName + " where id=" + id;
        Statement stmt = conn.createStatement();
        try {
            stmt.execute(sql);
        } catch (SQLException e) {
            throw e;
        } finally {
            close(conn, stmt);
        }
    }

    /**
     * @param @param  tableName
     * @param @param  colomn
     * @param @param  value
     * @param @throws SQLException
     * @return void
     * @throws
     * @Description: 通过表明和列名删除数据
     * @author Jahnke
     * @date 2016-3-23
     */
    public void delete(String tableName, String colomn, String value) throws SQLException {
        Connection conn = getConnection();
        String sql = "delete from " + tableName + " where " + colomn + "='" + value + "'";
        Statement stmt = conn.createStatement();
        try {
            stmt.execute(sql);
        } catch (SQLException e) {
            throw e;
        } finally {
            close(conn, stmt);
        }
    }

    /**
     * 执行sql语句，删除更新等操作
     *
     * @param sql
     * @throws SQLException
     */
    public void executeBySql(String sql) throws SQLException {
        Connection conn = getConnection();
        Statement stmt;
        stmt = conn.createStatement();
        // System.out.println(sql);
        try {
            stmt.execute(sql);
        } catch (SQLException e) {
            throw e;
        } finally {
            close(conn, stmt);
        }
    }

    public int executeUpdateBySql(String sql) throws SQLException {
        Connection conn = getConnection();
        Statement stmt = null;
        stmt = conn.createStatement();
        int i = 0;
        try {
            i = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            throw e;
        } finally {
            close(conn, stmt);
        }
        return i;
    }

    public int executeUpdateBySql(String sql, Connection conn)
            throws SQLException {
        Statement stmt;
        stmt = conn.createStatement();
        int i = stmt.executeUpdate(sql);
        close(conn, stmt);
        return i;
    }

    /**
     * 通过conn执行sql
     *
     * @param sql
     * @param conn
     * @throws SQLException
     */
    public void executeBySql(String sql, Connection conn)
            throws SQLException {
        if (conn != null) {
            Statement stmt;
            stmt = conn.createStatement();
            stmt.execute(sql);
        } else {
            // 当前conn为空
            throw new SQLException();
        }
    }

    /**
     * 事物回滚
     *
     * @param conn
     */
    public void rollback(Connection conn) {
        if (conn != null) {
            try {
                conn.rollback();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }

    /**
     * 判断当前返回的数据是否存在某一列 Jahnke 2016年3月30日 16:35:54
     *
     * @param rs
     * @param columnName
     * @return
     */
    private static boolean isExistColumn(ResultSet rs, String columnName) {
        try {
            if (rs.findColumn(columnName) > 0) {
                return true;
            }
        } catch (SQLException e) {
            return false;
        }
        return false;
    }

    //--------------存储过程

    /**
     * 创建存储过程
     *
     * @param
     * @return
     * @throws SQLException
     */
    public boolean createProcedure(String sql) throws SQLException {
        Connection conn = getConnection();
        PreparedStatement stmt = null;
        try {
            stmt = conn.prepareStatement(sql);
            stmt.execute();
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt);
        }
        return true;
    }

    /**
     * 根据sql语句查询数据库
     *
     * @param
     * @return
     * @throws SQLException
     */
    public List<HashMap<String, Object>> queryByProcedure(String procedureName, String... params) throws SQLException {
        StringBuffer sb = new StringBuffer();
        sb.append("CALL ").append(procedureName).append(" (");
        boolean hasParam = false;
        for (String property : params) {
            if (hasParam) {
                sb.append(",");
            }
            sb.append("'").append(property).append("'");
            hasParam = true;

        }
        sb.append(")");

        Connection conn = getConnection();
        List<HashMap<String, Object>> list = new ArrayList<HashMap<String, Object>>();
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sb.toString());
            ResultSetMetaData md = rs.getMetaData();
            int num = md.getColumnCount();
            while (rs.next()) {
                HashMap<String, Object> map = new HashMap<String, Object>();
                for (int i = 1; i <= num; i++) {
                    map.put(md.getColumnLabel(i), rs.getObject(i));
                }
                list.add(map);
            }
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            close(conn, stmt, rs);
        }
        return null;
    }

    private Object getObj(Object obj, Field[] fields, ResultSet rs) throws Exception {
        for (Field fd : fields) {
            fd.setAccessible(true);
            Class type = fd.getType();
            String columnName = fd.getName();
            // 首先判断时候存在此列，不存在则不赋值
            if (!isExistColumn(rs, columnName)) {
                continue;
            }
            if (type == Long.class || type == long.class) { // 如果参数为long类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                fd.set(obj, rs.getLong(columnName));
            } else if (type == String.class) { // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                fd.set(obj, rs.getString(columnName));
            } else if (type == int.class || type == Integer.class) {
                fd.set(obj, rs.getInt(columnName));
            } else if (type == BigInteger.class) {
                fd.set(obj, String.valueOf(rs.getBigDecimal(columnName)));
            } else if (type == Date.class) {
                if (rs.getDate(columnName) == null) {
                } else {
                    fd.set(obj, new Date(rs.getTimestamp(columnName).getTime()));
                }
            } else if (type == Double.class || type == double.class) {
                fd.set(obj, rs.getDouble(columnName));
            }
        }

        return obj;
    }

    private Object getObj(Object obj, Method[] methods, ResultSet rs) throws Exception {
        for (Method method : methods) {
            String methodName = method.getName();
            if (methodName.startsWith("set")) {
                // 根据方法名字得到数据表格中字段的名字
                String columnName = methodName.substring(3, 4).toLowerCase() + methodName.substring(4);

                // 首先判断时候存在此列，不存在则不赋值
                if (!isExistColumn(rs, columnName)) {
                    continue;
                }

                // 得到方法的参数类型
                Class[] parmts = method.getParameterTypes();
                if (parmts[0] == Long.class || parmts[0] == long.class) {
                    // 如果参数为long类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                    method.invoke(obj, rs.getLong(columnName));
                    if (columnName.equals("createTim")) {         //

                    }
                }
                if (parmts[0] == String.class) {
                    // 如果参数为String类型，则从结果集中按照列名取得对应的值，并且执行改set方法
                    method.invoke(obj, rs.getString(columnName));
                }
                if (parmts[0] == int.class || parmts[0] == Integer.class) {
                    method.invoke(obj, rs.getInt(columnName));
                }
                if (parmts[0] == BigInteger.class) {
                    method.invoke(obj, String.valueOf(rs.getBigDecimal(columnName)));
                }
                if (parmts[0] == Date.class) {
                    if (rs.getDate(columnName) == null) {
                    } else {
                        method.invoke(obj, new Date(rs.getTimestamp(columnName).getTime()));
                    }
                }
                if (parmts[0] == Double.class || parmts[0] == double.class) {
                    method.invoke(obj, rs.getDouble(columnName));
                }
            }
        }

        return obj;
    }

}

